﻿using MySql.Data.MySqlClient;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Collections.Generic;
using WorldDesign.Datas.Pocos;
using NPOI.Util;
using System.Threading.Tasks;
using XCode.DataAccessLayer;

namespace WorldDesign.CmdExecutor
{
    /// <summary>
    ///
    /// </summary>
    public class BackupExecutorService
    {
        static void BackupMain()
        {
            string sourceConnectionString = CaseProject.Meta.ConnName; // 替换为源数据库连接字符串
            string backupConnectionString = "your_backup_connection_string"; // 替换为备份数据库连接字符串

            // 获取源数据库中所有的表
            List<string> tableNames = GetTableNames(sourceConnectionString);

            // 对每个表生成备份SQL并插入到备份数据库中
            foreach (var tableName in tableNames)
            {
                GenerateAndExecuteBackupSQL(sourceConnectionString, backupConnectionString, tableName);
            }
            Console.WriteLine("所有表的备份操作已完成！");
        }

        // 获取源数据库中的所有表名
        static  List<string> GetTableNames(string connectionString)
        {
            List<string> tableNames = new List<string>();
            var dal = DAL.Create(CaseProject.Meta.ConnName);
            string query = "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'";
            var reader = dal.ExecuteReader(query);
            while (reader.Read())
            {
                tableNames.Add(reader.GetString(0));
            }
            //using (SqlConnection connection = new SqlConnection(connectionString))
            //{
            //    string query = "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'";
            //    SqlCommand command = new SqlCommand(query, connection);
            //    connection.Open();
            //    SqlDataReader reader = command.ExecuteReader();
            //    while (reader.Read())
            //    {
            //        tableNames.Add(reader.GetString(0));
            //    }
            //}
            

            return tableNames;
        }

        // 为每个表生成备份SQL并插入到备份数据库中
        static void GenerateAndExecuteBackupSQL(string sourceConnectionString, string backupConnectionString, string tableName)
        {
            // 先获取表结构
            List<string> columnNames = GetColumnNames(sourceConnectionString, tableName);

            if (columnNames.Count == 0) return;

            // 生成查询源数据的SQL
            string selectQuery = $"SELECT * FROM {tableName}";

            // 获取源数据
            List<List<object>> rows = GetDataFromSource(sourceConnectionString, selectQuery);

            if (rows.Count == 0) return;

            // 生成备份数据的INSERT SQL语句
            string insertQuery = GenerateInsertQuery(tableName, columnNames, rows);

            // 执行备份SQL
            ExecuteBackupSQL(backupConnectionString, insertQuery);
        }

        // 获取表的所有列名
        static List<string> GetColumnNames(string connectionString, string tableName)
        {
            List<string> columnNames = new List<string>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'";

                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    columnNames.Add(reader.GetString(0));
                }
            }

            return columnNames;
        }

        // 获取源数据库中的数据
        static List<List<object>> GetDataFromSource(string connectionString, string query)
        {
            List<List<object>> rows = new List<List<object>>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    List<object> row = new List<object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row.Add(reader.GetValue(i));
                    }

                    rows.Add(row);
                }
            }

            return rows;
        }

        // 生成插入备份数据库的SQL
        static string GenerateInsertQuery(string tableName, List<string> columnNames, List<List<object>> rows)
        {
            string insertQuery = $"INSERT INTO {tableName} ({string.Join(", ", columnNames)}) VALUES ";

            foreach (var row in rows)
            {
                string values = string.Join(", ", row.ConvertAll(v => v is string ? $"'{v}'" : v.ToString()));
                insertQuery += $"({values}),";
            }

            // 移除最后一个多余的逗号
            insertQuery = insertQuery.TrimEnd(',');

            return insertQuery;
        }

        // 执行插入备份数据库的SQL
        static void ExecuteBackupSQL(string backupConnectionString, string insertQuery)
        {
            using (SqlConnection connection = new SqlConnection(backupConnectionString))
            {
                SqlCommand command = new SqlCommand(insertQuery, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }


    }


}